# !/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
import os
import psycopg2
import csv

ROOT_PATH = os.path.abspath(os.path.join(os.path.dirname(__file__), os.path.pardir))
if ROOT_PATH not in sys.path:
    sys.path.append(ROOT_PATH)

from company_crawler.company_crawler_util import get_db_config

reload(sys)
sys.setdefaultencoding('utf-8')

db_config = get_db_config(ROOT_PATH)

conn = psycopg2.connect(database=db_config['db'], host=db_config['host'], port=db_config['port'],
                                     user=db_config['user'], password=db_config['pw'])
cur = conn.cursor()

# 查出公司对应的primary_id,通过company_brief, company_mapping_industry, company_relation_industry_primary_industry三张表, 在关系表中是通过industry_name去找的primary_id
def select_pri_industry_id():
      sql = "SELECT brief.id, pri_industry.pri_id FROM company_brief AS brief " \
            "LEFT JOIN " \
            "     (SELECT indu.id AS indu_id, re.primary_industry_id AS pri_id " \
            "     FROM company_mapping_industry AS indu " \
            "     LEFT JOIN company_relation_industry_primary_industry AS re " \
            "     ON indu.name=re.industry_name) " \
            "     AS pri_industry " \
            "ON brief.industry_id=pri_industry.indu_id;"

      cur.execute(sql)
      res = cur.fetchall()

      brief_id_to_pri_industry_id_dict = {}
      for single in res:
          brief_id_to_pri_industry_id_dict[single[0]] = single[1]
      return brief_id_to_pri_industry_id_dict


def update_pri_industry_id(pri_industry_id_dict):
      sql = "UPDATE company_brief SET primary_industry_id=%s WHERE id=%s"
      total = 0
      for brief_id, pri_industry_id in pri_industry_id_dict.items():
            total += 1
            try:
                  cur.execute(sql, (pri_industry_id, brief_id))
                  print 'Update total--%d  brief_id-%d ' % (total, brief_id)
            finally:
                  conn.commit()


def main():
      brief_id_to_pri_industry_id_dict = select_pri_industry_id()
      update_pri_industry_id(brief_id_to_pri_industry_id_dict)


def insert_relation_table(filepath, sql):
      with open(filepath, 'r') as csvfile:
            reader = csv.reader(csvfile)
            for line in reader:
                  cur.execute(sql, line)
                  conn.commit()


if __name__ == '__main__':
      # sql_relateion = 'INSERT INTO company_relation_industry_primary_industry (primary_industry_id, primary_industry_name, industry_id, industry_name) VALUES (%s, %s, %s, %s)'
      # sql_mapping = 'INSERT INTO company_mapping_primary_industry (id, name, display_name) VALUES (%s, %s, %s)'
      # insert_relation_table('/home/xyh/my_cninfo_crawler/relationship_industry.csv', sql_relateion)
      # insert_relation_table('/home/xyh/my_cninfo_crawler/company_mapping_primary_industry_id.csv', sql_mapping)
      main()




